import { executeSql } from "../util/db.js";


export const selectArticlesGetCount = function (startindex, pageSize, type, key) {
    let sql = 'select * from articles where isDelete=0';
    let params = [];

    if (type != null && type != '') {
        sql += ` and articleId=? `;
        params.push(type);
    };

    if (key != null && key != '') {
        sql += ' and title like ?';
        params.push('%' + key + '%');
    };

    sql += ' limit ?,?;'

    params.push(startindex);
    params.push(pageSize);

    return executeSql(sql, params);
}

export const selectArticlescount = function () {
    let sql = "select count(id) count from articles";
    return executeSql(sql);
};

export const selectArticlesRandom = function (start = 0) {
    let sql = "select id,cover,title from articles limit ?,5";
    return executeSql(sql, [start]);
}

export const selectArticlesHot = function (start = 0) {
    let sql = "select id,title,`read` from articles order by `read` desc limit 0,5";
    return executeSql(sql, [start]);
}

export const selectArticlesNew = function () {
    let sql = "select a.id,a.title,substring(a.content,20,200) content,a.cover,b.`name`,a.`read`, count(a.id) comments,a.`date` from articles a left join comments c on a.id = c.articleId left join categories b on a.categoryId = b.id GROUP BY a.id order by a.`date` desc limit 0,5;";
    return executeSql(sql);
}

export const selectArticlesContent = function (start = 0) {
    let sql = "select substring(content,20,200) from articles limit ?,7;"
    return executeSql(sql, [start]);
}


export const selectArticlesCountId = function (id) {
    let sql = `
    select a.title,a.author,b.name type,a.date,a.read,count(a.id) comments,a.content 
    from articles a left join categories b 
    on a.categoryId=b.id 
    left join comments c on a.id=c.articleId
    where a.isDelete=0 and a.id=?;`
    return executeSql(sql, [id]);
}

export const selectArticlesPre = function (cid) {
    let sql = 'select id,title from articles where isDelete=0 and id<? order by id desc limit 1;';
    return executeSql(sql, [cid]);
}

export const selectArticlesNext = function (cid) {
    let sql = 'select id,title from articles where isDelete=0 and id>? order by id  limit 1;';
    return executeSql(sql, [cid]);
}

export const selectArticlesCateId = function (cid) {
    let sql = 'select * from articles where articleId = ?;';
    return executeSql(sql, [cid]);
}

export const selectArticlesId = function (id) {
    let sql = 'select id,title,cover,categoryId,date,content from articles where id = ?;';
    return executeSql(sql, [id]);
}

export const selectArticlesNewContent = function () {
    let sql = 'select date,count(date) 日新增文章数量 from articles group by date;';
    return executeSql(sql);
}

export const selectArticlesCategory = function () {
    let sql = 'select c.`name`, count(a.categoryId) articles from articles a left join categories c on a.categoryId = c.id group by c.id;';
    return executeSql(sql);
}

export const selectArticlesDatecontent = function () {
    let sql = 'select `date` 日期,`read` 访问数量 from articles order by `date` desc limit 7;';
    return executeSql(sql);
}

export const selectArticlesByPage = function (startIndex, pageSize) {
    let sql = 'select c.id, c.author, c.content, a.id aid, a.title, c.date, c.state from comments c left join articles a on c.id = a.categoryId where a.isDelete = 0 order by c.date desc limit ?,?;';
    // let params = [startIndex, pageSize];
    return executeSql(sql, [startIndex, pageSize]);
}

export const deleteArticle = function (id) {
    let sql = 'delete from articles where id = ?;';
    return executeSql(sql, [id]);
}

export const selectAllArticlesComments = function () {
    let sql = `SELECT '总文章数量' type, COUNT(id) value FROM articles
    UNION ALL
    (SELECT '日新增文章数' type, COUNT(date) value FROM articles GROUP BY date order by date desc limit 1)
    UNION ALL
    SELECT '总评论数'  type, COUNT(*) value FROM comments
    UNION ALL
    (SELECT '日新增评论数' type, COUNT(date) value FROM comments GROUP BY date order by date desc limit 1);`;
    return executeSql(sql);
}

